USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[PlayerShortStatsSelectByPlayerId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.PlayerShortStatsSelectByPlayerId
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/10/08
-- Description:	PlayerShortStats Table Select By PlayerId
-- =============================================
CREATE PROCEDURE PlayerShortStatsSelectByPlayerId

			@PlayerId varchar(30)
	
AS
BEGIN

DECLARE 
		@ReturnName varchar(50),
		@Points varchar(8),
		@Kills int,
		@Assists int,
		@Deaths int,
		@KillDeathRatio varchar(8),
		@Ubers int,
		@Healing float,
		@Captures int,
		@FlagsCaptured int,
		@Dominations int
		
		

select @Assists = COUNT(AssistId) FROM Assists WHERE assister_PlayerId = @PlayerId

-- Player Name
SELECT @ReturnName = PlayerName FROM 
	(SELECT COUNT(PlayerName) AS Frequency, PlayerName, PlayerId as PlayerID 
		FROM PlayerNameList
		GROUP BY PlayerName, PlayerId ) as NewTable
	WHERE PlayerId = @PlayerID 
	GROUP BY PlayerName


select @Kills = COUNT(KillId) FROM Kills WHERE Attacker_PlayerId = @PlayerId

select @Deaths = COUNT(KillId) FROM Kills WHERE Victim_PlayerId = @PlayerId
	AND Victim_classId != (select top 1 ClassId from classes WHERE ClassShortName = 'medic')

select @Captures = COUNT(CaptureListId) FROM CaptureList 
		WHERE PlayerId = @PlayerId

select @Dominations  = COUNT(DominationId) FROM Dominations
		WHERE Attacker_PlayerId = @PlayerId

select @FlagsCaptured = COUNT(FlagId) FROM Flag 
		WHERE PlayerId = @PlayerId
		AND FlagEventId = (SELECT FlagEventId FROM FlagEventType WHERE FlagEventShortName = 'captured')

select @Ubers  = COUNT(ChargeId) FROM MedicCharge
		WHERE PlayerId = @PlayerId


SET @Healing = 0.0


select @ReturnName as [PlayerName], 0 as [Points], 
		@Kills as [Kills], @Assists as [Assists], 
		@Deaths as [Deaths], 0.0 as [KillDeathRatio],
		@Dominations as [Dominations], @Ubers as [Ubers],
		@Captures as [Captures], @FlagsCaptured as [FlagsCaptured],
		@PlayerId as [PlayerId]

END
GO

--    PlayerShortStatsSelectByPlayerId 'STEAM_0:0:13191839'



